﻿using System.IO;
using BomComparison.Core.Interface;
using BomComparison.Core.Model;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Color = System.Drawing.Color;

namespace Excel.EPPlus;

public class ExcelWriter : IExcelWriter
{
    // 添加文件导出路径属性
    public string ExportPath { get; set; }
    public string FileName { get; set; } = "BOM差异报告.xlsx";

    public List<ComparisonResultByPartNumber> ComparisonResultByPartNumber { get; set; }
    public List<ComparisonResultByPartReference> ComparisonResultByPartReference { get; set; }

    // 组合 ExportPath 和 FileName 的完整路径
    private string FullExportPath
    {
        get
        {
            if (string.IsNullOrEmpty(ExportPath))
            {
                throw new InvalidOperationException("导出路径未设置，请先设置 ExportPath 属性。");
            }

            return Path.Combine(ExportPath, FileName);
        }
    }





    // 构造函数


    // 构造函数
    public ExcelWriter()
    {




    }

    //---------------------------------------------------------------------

    public void ExportFromPath(string exportPath, string fileName)
    {
        // 参数校验
        if (string.IsNullOrWhiteSpace(exportPath))
            throw new ArgumentException("导出路径不能为空", nameof(exportPath));
        if (string.IsNullOrWhiteSpace(fileName))
            throw new ArgumentException("文件名不能为空", nameof(fileName));

        ExportPath = exportPath;
        FileName = fileName;
        // 确保目标目录存在
        var fullExportPath = new FileInfo(FullExportPath);
        if (!fullExportPath.Directory.Exists)
            fullExportPath.Directory.Create();

        if (fullExportPath.Exists)
        {
            fullExportPath.Delete();
        }

        // 设置 EPPlus 许可模式（根据实际版本调整）
        try
        {
            ExcelPackage.License.SetNonCommercialPersonal("ZHJEE");
            Console.WriteLine("EPPlus 已初始化为非商业许可模式。");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"设置 EPPlus 许可失败: {ex.Message}");
            throw;
        }
        try
        {
            using var xlPackage = new ExcelPackage(fullExportPath);
            var workbook = xlPackage.Workbook;

            // Sheet 1: readme
            var worksheet1 = workbook.Worksheets.Add("readme");
            SetSheet1Title(worksheet1);
            //SetSheet1ColumnHeaders(worksheet1);
            SetSheet1PageHeader(worksheet1);
            SetSheet1PageFooter(worksheet1);

            // Sheet 2: EC变更报告
            var worksheet2 = workbook.Worksheets.Add("EC变更报告");
            GenerateBOMHeaderTemplate(xlPackage, worksheet2);
            // Export AddedByPartNumber
            ExportBomItemsToTemplate(worksheet2, ComparisonResultByPartNumber);

            // Sheet 3: 变更详情 按Part Number汇总
            var worksheet3 = workbook.Worksheets.Add("变更详情 按Part Number汇总");
            SetSheet3Title(worksheet3);
            SetSheet3ColumnHeaders(worksheet3);
            SetSheet3PageHeader(worksheet3);
            SetSheet3PageFooter(worksheet3);

            // Sheet 4: 变更详情 按Part Reference汇总
            var worksheet4 = workbook.Worksheets.Add("变更详情 按Part Reference汇总");
            SetSheet4Title(worksheet4);
            SetSheet4ColumnHeaders(worksheet4);
            SetSheet4PageHeader(worksheet4);
            SetSheet4PageFooter(worksheet4);
            ExportBomItemsToTemplate1(worksheet4, ComparisonResultByPartReference);






            SaveExcelPackage(xlPackage, FullExportPath);

        }
        catch (Exception ex)
        {
            Console.WriteLine($"导出 Excel 过程中发生异常: {ex.Message}");
            throw;
        }

    }



    /// <summary>
    /// 生成固定的 BOM 表头模板
    /// </summary>
    private void GenerateBOMHeaderTemplate(ExcelPackage package, ExcelWorksheet worksheet)
    {
        //插入图片
        //var image = @"E:\02_soft_project\AB00001_BOMCompare\BOMCompareTool\BOM_TOOL\Epplus.Extensions\Resources\ms-icon-310x310.png";

        //ExcelAddpicture.InsertImage(worksheet, image, maxRows + 5, 10, true);

        AddFileProperties(package);
        // 设置列标题
        SetSheet2Title(worksheet);
        SetSheet2ColumnHeaders(worksheet);
        SetSheet2PageHeader(worksheet);
        SetSheet2PageFooter(worksheet);
        // 自动调整列宽
        //worksheet.Cells.AutoFitColumns();
    }

    #region 生成 "readme" sheet 表头

    private void SetSheet1Title(ExcelWorksheet worksheet)
    {
        worksheet.Cells["A1"].Value = "免责声明";

        //合并A1:H1单元格
        using (var range = worksheet.Cells["A1:R1"])
        {
            range.Merge = true;

            //设置字体和大小
            range.Style.Font.SetFromFont("Britannic Bold", 22, false, true);
            range.Style.Font.Bold = true;
            range.Style.Font.Color.SetColor(Color.Black);

            //设置水平和垂直对齐方式
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

            //设置填充颜色
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
        }

        worksheet.Cells["A2"].Value =

            "尽管作者已对软件进行测试，但不保证与所有硬件/软件环境完全兼容，亦不承诺不存在任何缺陷。" +
            "因使用或误用本软件导致的数据丢失、系统损坏、经济损失等后果，作者概不负责。" +
            "作者无义务提供软件维护、更新或技术支持服务（包括BUG修复、功能升级等）。";

        using (var range = worksheet.Cells["A2:R5"])
        {
            range.Merge = true;

            //设置字体和大小
            range.Style.Font.SetFromFont("Arial", 10, false, true);
            range.Style.Font.Bold = true;
            range.Style.Font.Color.SetColor(Color.Black);

            //设置水平和垂直对齐方式
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Top;

            //设置自动换行
            range.Style.WrapText = true;

            //设置填充颜色
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
        }


        using (var range = worksheet.Cells["A1:R5"])
        {

            // 设置单元格所有边框
            range.Style.Border.BorderAround(ExcelBorderStyle.Thick, Color.Black);
        }


        worksheet.Cells["A6"].Value = "License";

        //合并A1:H1单元格
        using (var range = worksheet.Cells["A6:R6"])
        {
            range.Merge = true;

            //设置字体和大小
            range.Style.Font.SetFromFont("Britannic Bold", 22, false, true);
            range.Style.Font.Bold = true;
            range.Style.Font.Color.SetColor(Color.Black);

            //设置水平和垂直对齐方式
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;

            //设置填充颜色
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
        }

        // 设置版权信息
        worksheet.Cells["A7"].Value =
            "Copyright © 2025 ZHJEE\r\n\r\n" +
            "Permission is hereby granted, free of charge, to any person obtaining a copy of this software and" +
            " associated documentation files (the “Software”), to deal in the Software without restriction, " +
            "including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, " +
            "and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so," +
            " subject to the following conditions:\r\n\r\n" +
            "The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.\r\n\r\n" +
            "THE SOFTWARE IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT " +
            "NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT." +
            " IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY," +
            " WHETHER IN AN ACTION OF CONTRACT,TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE" +
            " OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.\r\n\r\n";
        using (var range = worksheet.Cells["A7:R19"])
        {
            range.Merge = true;

            //设置字体和大小
            range.Style.Font.SetFromFont("Arial", 10, false, true);
            range.Style.Font.Bold = true;
            range.Style.Font.Color.SetColor(Color.Black);

            //设置水平和垂直对齐方式
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Top;

            //设置自动换行
            range.Style.WrapText = true;

            //设置填充颜色
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
        }

        // 设置版权信息
        worksheet.Cells["A20"].Value =
            "Copyright © 2025 ZHJEE\r\n\r\n" +
            "特此免费授予任何获得本软件副本和相关文档文件（下称“软件”）的人不受限制地处置" +
            "该软件的权利，包括不受限制地使用、复制、修改、合并、发布、分发、转授许可和/或" +
            "出售该软件副本，以及再授权被配发了本软件的人如上的权利，须在下列条件下：\r\n\r\n" +
            "上述版权声明和本许可声明应包含在该软件的所有副本或实质成分中。\r\n\r\n" +
            "本软件是“如此”提供的，没有任何形式的明示或暗示的保证，包括但不限于对适销性、特" +
            "定用途的适用性和不侵权的保证。在任何情况下，作者或版权持有人都不对任何索赔、损" +
            "害或其他责任负责，无论这些追责来自合同、侵权或其它行为中，还是产生于、源于或有" +
            "关于本软件以及本软件的使用或其它处置。";


        using (var range = worksheet.Cells["A20:R30"])
        {
            range.Merge = true;

            //设置字体和大小
            range.Style.Font.SetFromFont("Arial", 10, false, true);
            range.Style.Font.Bold = true;
            range.Style.Font.Color.SetColor(Color.Black);

            //设置水平和垂直对齐方式
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Top;

            //设置自动换行
            range.Style.WrapText = true;

            //设置填充颜色
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            range.Style.Fill.BackgroundColor.SetColor(Color.AliceBlue);
        }

        using (var range = worksheet.Cells["A1:R30"])
        {

            // 设置单元格所有边框
            range.Style.Border.BorderAround(ExcelBorderStyle.Thick, Color.Black);
        }

    }



    private void SetSheet1PageHeader(ExcelWorksheet worksheet)
    {

        //设置打印页面的标题头
        // lets set the header text 
        worksheet.HeaderFooter.OddHeader.CenteredText = "EC 变更报告";

    }

    private void SetSheet1PageFooter(ExcelWorksheet worksheet)
    {
        // add the page number to the footer plus the total number of pages
        worksheet.HeaderFooter.OddFooter.RightAlignedText =
            string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);

        // add the sheet name to the footer
        worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;

        // add the file path to the footer
        worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FileName;
    }

    #endregion


    #region 生成 "EC变更报告" sheet 表头

    private void SetSheet2Title(ExcelWorksheet worksheet)
    {
        worksheet.Cells["A1"].Value = "BOM 差异报告";

        //合并A1:H1单元格
        using (var r = worksheet.Cells["A1:R1"])
        {
            r.Merge = true;
            //range.Style.Font.SetFromFont("Britannic Bold", 22, false, true);
            r.Style.Font.Color.SetColor(Color.Black);
            r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            r.Style.Fill.PatternType = ExcelFillStyle.Solid;
            //range.Style.Fill.BackgroundColor.SetColor(Color.Honeydew);
            r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(240, 255, 240));
        }

    }



    private void SetSheet2ColumnHeaders(ExcelWorksheet worksheet)
    {
        //设置列标题以及格式
        using (var range = worksheet.Cells["A3:R3"])
        {
            range.Style.Font.Bold = true;
            //range.Style.Font.SetFromFont("黑体", 10, false, false);
            range.Style.Font.SetFromFont("黑体", 10);
            range.Style.Font.Color.SetColor(Color.Black);
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            // 浅蓝色背景
            //range.Style.Fill.BackgroundColor.SetColor(Color.Honeydew);
            range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));



            // 设置单元格所有边框
            range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Top.Color.SetColor(Color.Black);

            range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Bottom.Color.SetColor(Color.Black);

            range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Left.Color.SetColor(Color.Black);

            range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Right.Color.SetColor(Color.Black);


            //设置范围外围边框
            range.Style.Border.BorderAround(ExcelBorderStyle.Thick, Color.Black);

        }


        // 设置列标题内容
        worksheet.Cells["A3"].Value = "父项编码";
        worksheet.Cells["B3"].Value = "中文名称";
        worksheet.Cells["C3"].Value = "操作类型";
        worksheet.Cells["D3"].Value = "物料编码";
        worksheet.Cells["E3"].Value = "原数量";
        worksheet.Cells["F3"].Value = "变更后物料编码";
        worksheet.Cells["G3"].Value = "现数量";
        worksheet.Cells["H3"].Value = "修订后的最终位号";
        worksheet.Cells["I3"].Value = "现工序";
        worksheet.Cells["J3"].Value = "取替代";
        worksheet.Cells["K3"].Value = "替代ITEM编码";
        worksheet.Cells["L3"].Value = "备注";
        worksheet.Cells["M3"].Value = "替代关系";
        worksheet.Cells["N3"].Value = "生效时间";
        worksheet.Cells["O3"].Value = "失效时间";
        worksheet.Cells["P3"].Value = "EC计划执行时间";
        worksheet.Cells["Q3"].Value = "主键";
        worksheet.Cells["R3"].Value = "行号";

        // 设置列宽
        worksheet.Column(1).Width = 10;  // 父项编码
        worksheet.Column(2).Width = 20; // 中文名称
        worksheet.Column(3).Width = 10; // 操作类型
        worksheet.Column(4).Width = 15; // 物料编码
        worksheet.Column(5).Width = 10; // 原数量
        worksheet.Column(6).Width = 15; // 变更后物料编码
        worksheet.Column(7).Width = 10; // 现数量
        worksheet.Column(8).Width = 20; // 修订后的最终位号
        worksheet.Column(9).Width = 10; // 现工序
        worksheet.Column(10).Width = 10; // 取替代
        worksheet.Column(11).Width = 15; // 替代ITEM编码
        worksheet.Column(12).Width = 30; // 备注
        worksheet.Column(13).Width = 10; // 替代关系
        worksheet.Column(14).Width = 10; // 生效时间
        worksheet.Column(15).Width = 10; // 失效时间
        worksheet.Column(16).Width = 15; // EC计划执行时间
        worksheet.Column(17).Width = 10; // 主键
        worksheet.Column(18).Width = 10; // 行号
    }



    private void SetSheet2PageHeader(ExcelWorksheet worksheet)
    {

        //设置打印页面的标题头
        // lets set the header text 
        worksheet.HeaderFooter.OddHeader.CenteredText = "EC 变更报告";

    }

    private void SetSheet2PageFooter(ExcelWorksheet worksheet)
    {
        // add the page number to the footer plus the total number of pages
        worksheet.HeaderFooter.OddFooter.RightAlignedText =
            string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);

        // add the sheet name to the footer
        worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;

        // add the file path to the footer
        worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FileName;
    }

    #endregion

    #region 生成 "变更详情 按Part Number汇总" sheet 表头

    private void SetSheet3Title(ExcelWorksheet worksheet)
    {
        worksheet.Cells["A1"].Value = "BOM 差异报告";

        //合并A1:H1单元格
        using (var r = worksheet.Cells["A1:R1"])
        {
            r.Merge = true;
            //range.Style.Font.SetFromFont("Britannic Bold", 22, false, true);
            r.Style.Font.Color.SetColor(Color.Black);
            r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            r.Style.Fill.PatternType = ExcelFillStyle.Solid;
            //range.Style.Fill.BackgroundColor.SetColor(Color.Honeydew);
            r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(240, 255, 240));
        }

    }



    private void SetSheet3ColumnHeaders(ExcelWorksheet worksheet)
    {
        //设置列标题以及格式
        using (var range = worksheet.Cells["A3:R3"])
        {
            range.Style.Font.Bold = true;
            //range.Style.Font.SetFromFont("黑体", 10, false, false);
            range.Style.Font.SetFromFont("黑体", 10);
            range.Style.Font.Color.SetColor(Color.Black);
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            // 浅蓝色背景
            //range.Style.Fill.BackgroundColor.SetColor(Color.Honeydew);
            range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));



            // 设置单元格所有边框
            range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Top.Color.SetColor(Color.Black);

            range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Bottom.Color.SetColor(Color.Black);

            range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Left.Color.SetColor(Color.Black);

            range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Right.Color.SetColor(Color.Black);


            //设置范围外围边框
            range.Style.Border.BorderAround(ExcelBorderStyle.Thick, Color.Black);

        }


        // 设置列标题内容
        worksheet.Cells["A3"].Value = "父项编码";
        worksheet.Cells["B3"].Value = "中文名称";
        worksheet.Cells["C3"].Value = "操作类型";
        worksheet.Cells["D3"].Value = "物料编码";
        worksheet.Cells["E3"].Value = "原数量";
        worksheet.Cells["F3"].Value = "变更后物料编码";
        worksheet.Cells["G3"].Value = "现数量";
        worksheet.Cells["H3"].Value = "修订后的最终位号";
        worksheet.Cells["I3"].Value = "现工序";
        worksheet.Cells["J3"].Value = "取替代";
        worksheet.Cells["K3"].Value = "替代ITEM编码";
        worksheet.Cells["L3"].Value = "备注";
        worksheet.Cells["M3"].Value = "替代关系";
        worksheet.Cells["N3"].Value = "生效时间";
        worksheet.Cells["O3"].Value = "失效时间";
        worksheet.Cells["P3"].Value = "EC计划执行时间";
        worksheet.Cells["Q3"].Value = "主键";
        worksheet.Cells["R3"].Value = "行号";

        // 设置列宽
        worksheet.Column(1).Width = 10;  // 父项编码
        worksheet.Column(2).Width = 20; // 中文名称
        worksheet.Column(3).Width = 10; // 操作类型
        worksheet.Column(4).Width = 15; // 物料编码
        worksheet.Column(5).Width = 10; // 原数量
        worksheet.Column(6).Width = 15; // 变更后物料编码
        worksheet.Column(7).Width = 10; // 现数量
        worksheet.Column(8).Width = 20; // 修订后的最终位号
        worksheet.Column(9).Width = 10; // 现工序
        worksheet.Column(10).Width = 10; // 取替代
        worksheet.Column(11).Width = 15; // 替代ITEM编码
        worksheet.Column(12).Width = 30; // 备注
        worksheet.Column(13).Width = 10; // 替代关系
        worksheet.Column(14).Width = 10; // 生效时间
        worksheet.Column(15).Width = 10; // 失效时间
        worksheet.Column(16).Width = 15; // EC计划执行时间
        worksheet.Column(17).Width = 10; // 主键
        worksheet.Column(18).Width = 10; // 行号
    }



    private void SetSheet3PageHeader(ExcelWorksheet worksheet)
    {

        //设置打印页面的标题头
        // lets set the header text 
        worksheet.HeaderFooter.OddHeader.CenteredText = "EC 变更报告";

    }

    private void SetSheet3PageFooter(ExcelWorksheet worksheet)
    {
        // add the page number to the footer plus the total number of pages
        worksheet.HeaderFooter.OddFooter.RightAlignedText =
            string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);

        // add the sheet name to the footer
        worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;

        // add the file path to the footer
        worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FileName;
    }

    #endregion

    #region 生成 "变更详情 按Part Reference汇总" sheet 表头
    private void SetSheet4Title(ExcelWorksheet worksheet)
    {
        worksheet.Cells["A1"].Value = "BOM 差异报告 按Part Reference汇总";

        //合并A1:H1单元格
        using (var r = worksheet.Cells["A1:R1"])
        {
            r.Merge = true;
            //range.Style.Font.SetFromFont("Britannic Bold", 22, false, true);
            r.Style.Font.Color.SetColor(Color.Black);
            r.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            r.Style.Fill.PatternType = ExcelFillStyle.Solid;
            //range.Style.Fill.BackgroundColor.SetColor(Color.Honeydew);
            r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(240, 255, 240));
        }

    }



    private void SetSheet4ColumnHeaders(ExcelWorksheet worksheet)
    {
        //设置列标题以及格式
        using (var range = worksheet.Cells["A3:R3"])
        {
            range.Style.Font.Bold = true;
            //range.Style.Font.SetFromFont("黑体", 10, false, false);
            range.Style.Font.SetFromFont("黑体", 10);
            range.Style.Font.Color.SetColor(Color.Black);
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
            range.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
            // 浅蓝色背景
            //range.Style.Fill.BackgroundColor.SetColor(Color.Honeydew);
            range.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));



            // 设置单元格所有边框
            range.Style.Border.Top.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Top.Color.SetColor(Color.Black);

            range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Bottom.Color.SetColor(Color.Black);

            range.Style.Border.Left.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Left.Color.SetColor(Color.Black);

            range.Style.Border.Right.Style = ExcelBorderStyle.Thin;
            range.Style.Border.Right.Color.SetColor(Color.Black);


            //设置范围外围边框
            range.Style.Border.BorderAround(ExcelBorderStyle.Thick, Color.Black);

        }


        // 设置列标题内容
        worksheet.Cells["A3"].Value = "操作类型";
        worksheet.Cells["B3"].Value = "位号";
        worksheet.Cells["C3"].Value = "数量";
        worksheet.Cells["D3"].Value = "物料编码";
        worksheet.Cells["E3"].Value = "物料描述";
        worksheet.Cells["F3"].Value = "变更后物料编码";
        worksheet.Cells["G3"].Value = "变更后物料描述";


        // 设置列宽
        worksheet.Column(1).Width = 10;  // 操作类型
        worksheet.Column(2).Width = 20;  // 位号
        worksheet.Column(3).Width = 10;  // "数量
        worksheet.Column(4).Width = 15;  // 物料编码
        worksheet.Column(5).Width = 10;  // 物料描述
        worksheet.Column(6).Width = 15;  // 变更后物料编码
        worksheet.Column(7).Width = 10;  // 变更后物料描述

    }



    private void SetSheet4PageHeader(ExcelWorksheet worksheet)
    {

        //设置打印页面的标题头
        // lets set the header text 
        worksheet.HeaderFooter.OddHeader.CenteredText = "EC 变更报告";

    }

    private void SetSheet4PageFooter(ExcelWorksheet worksheet)
    {
        // add the page number to the footer plus the total number of pages
        worksheet.HeaderFooter.OddFooter.RightAlignedText =
            string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);

        // add the sheet name to the footer
        worksheet.HeaderFooter.OddFooter.CenteredText = ExcelHeaderFooter.SheetName;

        // add the file path to the footer
        worksheet.HeaderFooter.OddFooter.LeftAlignedText = ExcelHeaderFooter.FileName;
    }

    #endregion


    #region 设置文件属性

    private void AddFileProperties(ExcelPackage xlPackage)
    {

        // We can also add some document properties to the spreadsheet 
        // Set some core property values
        //设置文件属性

        xlPackage.Workbook.Properties.Title = "BOM 差异报告";
        xlPackage.Workbook.Properties.Subject = "自动化生成BOM变更报告";
        xlPackage.Workbook.Properties.Keywords = "Office Open XML";
        xlPackage.Workbook.Properties.Category = "报告";
        xlPackage.Workbook.Properties.Comments = "使用自动化软件生成的BOM差异报告";

        xlPackage.Workbook.Properties.Author = "ZHJEE";
        // Set some extended property values
        xlPackage.Workbook.Properties.Company = "ZHJEE";
        xlPackage.Workbook.Properties.HyperlinkBase = new Uri("https://ZHJEE.com");

        // Set some custom property values
        xlPackage.Workbook.Properties.SetCustomPropertyValue("Checked by", "ZHJEE");


    }
    #endregion

    private void ExportBomItemsToTemplate(ExcelWorksheet worksheet, List<ComparisonResultByPartNumber> comparisonResult)
    {
        int startRow = 4; // 从第4行开始写入数据
        foreach (var item in comparisonResult)
        {
            worksheet.Cells[startRow, 1].Value = "";                                                            // 父项编码
            worksheet.Cells[startRow, 2].Value = "";                                                            // 中文名称
            worksheet.Cells[startRow, 3].Value = item.ChangeType;                                               // 操作类型
            worksheet.Cells[startRow, 4].Value = item.OldBomItem?.PartNumber ?? "";                             // 物料编码
            worksheet.Cells[startRow, 5].Value = item.OldBomItem != null ? item.OldBomItem.PartQuantity : "";   // 原数量
            worksheet.Cells[startRow, 6].Value = item.NewBomItem?.PartNumber ?? "";                             // 变更后物料编码
            worksheet.Cells[startRow, 7].Value = item.NewBomItem != null ? item.NewBomItem.PartQuantity : "";   // 现数量
            worksheet.Cells[startRow, 8].Value = item.NewBomItem?.PartReference ?? "";                          // 修订后的最终位号
            worksheet.Cells[startRow, 9].Value = "";                                                            // 现工序
            worksheet.Cells[startRow, 10].Value = "";                                                           // 取替代
            worksheet.Cells[startRow, 11].Value = item.NewBomItem?.AlternatePNs ?? "";                          // 替代ITEM编码
            AddRichText(worksheet, startRow, 12, item.ChangedReferences.Added, item.ChangedReferences.Deleted);
            worksheet.Cells[startRow, 13].Value = "";                                                           // 替代关系
            worksheet.Cells[startRow, 14].Value = "";                                                           // 生效时间
            worksheet.Cells[startRow, 15].Value = "";                                                           // 失效时间
            worksheet.Cells[startRow, 16].Value = "";                                                           // EC计划执行时间
            worksheet.Cells[startRow, 17].Value = "";                                                           // 主键
            worksheet.Cells[startRow, 18].Value = "";                                                           // 行号

            startRow++;
        }
    }


    private void ExportBomItemsToTemplate1(ExcelWorksheet worksheet, List<ComparisonResultByPartReference> comparisonResult)
    {
        int startRow = 4; // 从第4行开始写入数据
        foreach (var item in comparisonResult)
        {
            worksheet.Cells[startRow, 1].Value = item.ChangeType;                                               // 操作类型
            worksheet.Cells[startRow, 2].Value = item.PartReference ?? "";                          // 位号
            worksheet.Cells[startRow, 3].Value = item.OldBomItem != null ? item.OldBomItem.PartQuantity : "";   // "数量
            worksheet.Cells[startRow, 4].Value = item.OldBomItem?.PartNumber ?? "";                             // 物料编码
            worksheet.Cells[startRow, 5].Value = item.OldBomItem?.PartDescription ?? "";                        // 物料描述
            worksheet.Cells[startRow, 6].Value = item.NewBomItem?.PartNumber ?? "";                             // 变更后物料编码
            worksheet.Cells[startRow, 7].Value = item.NewBomItem?.PartDescription ?? "";                        // 变更后物料描述


            startRow++;
        }
    }






    private void AddRichText(ExcelWorksheet worksheet, int row, int cowl, string addedText, string deletedText)

    {

        //Multiple Fonts in the same cell
        var cell = worksheet.Cells[row, cowl];
        cell.IsRichText = true;
        cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
        cell.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
        cell.Style.WrapText = true;


        if (!string.IsNullOrEmpty(addedText))
        {

            //ExcelRichText uses "using OfficeOpenXml.Style;"
            var added = cell.RichText.Add("添加位号：" + addedText);
            added.Bold = true;
            added.Italic = true;
            added.Color = Color.Blue;

            if (!string.IsNullOrEmpty(deletedText))
            {

                var deleted = cell.RichText.Add("\n" + "删除位号：" + deletedText);
                deleted.UnderLine = true;
                deleted.Bold = false;
                deleted.Color = Color.Red;
            }


        }

        else if (!string.IsNullOrEmpty(deletedText))
        {
            var text2 = cell.RichText.Add("删除位号：" + deletedText);
            text2.UnderLine = true;
            text2.Bold = false;
            text2.Color = Color.Red;

        }
        else
        {
            cell.RichText.Add("");


        }
    }

    private void SaveExcelPackage(ExcelPackage package, string path)
    {
        var fileInfo = new FileInfo(path);
        if (fileInfo.Exists)
        {
            fileInfo.Delete();
        }
        package.SaveAs(fileInfo);
    }








}
